package com.topinfo.etl.service;

import com.topinfo.component.base.bean.JsonResponse;
import com.topinfo.component.base.dao.IModelDao;
import com.topinfo.component.base.service.AbstractModelService;
import com.topinfo.etl.bean.EtlDatasourceCfg;
import com.topinfo.etl.bean.EtlSynctableCfg;
import com.topinfo.etl.bean.EtlSynctableCol;
import com.topinfo.etl.dao.EtlDatasourceCfgDao;
import com.topinfo.etl.dao.EtlSynctableCfgDao;
import com.topinfo.etl.util.DbUtil;
import com.topinfo.etl.util.FmTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.sql.*;
import java.util.*;

/**
* 数据源配置信息
*/
@Service("etlSynctableCfgService")
public class EtlSynctableCfgService extends AbstractModelService<EtlSynctableCfg> {
    @Resource
    private EtlSynctableCfgDao etlSynctableCfgDao;

    @Resource
    private EtlDatasourceCfgDao etlDatasourceCfgDao;

    @Override
    public IModelDao<EtlSynctableCfg> getModelDao() {
        return this.etlSynctableCfgDao;
    }

    public JsonResponse tableListSearch(String sourceId) {
        EtlDatasourceCfg model = etlDatasourceCfgDao.select(sourceId);
        String driver = DbUtil.getDriverClassName(model.getType());
        String dbUrl = DbUtil.getDbUrl(model.getType(), model.getServerAddr(), model.getPort(), model.getName());
        Connection conn = DbUtil.getDbConn(driver, dbUrl, model.getUser(), model.getPassword());
        if (conn != null) {
            String selectTableListSql = DbUtil.getSelectTableListSql(model.getType());
            PreparedStatement ps = null;
            ResultSet rs = null;
            try {
                ps = conn.prepareStatement(selectTableListSql);
                ps.setString(1, model.getName());
                rs = ps.executeQuery();
                Map map = new LinkedHashMap();
                List list = new ArrayList();
                while (rs.next()) {
                    String tableName = rs.getString("tableName");
                    String tableId = tableName;
                    EtlSynctableCfg etlSynctableCfg = new EtlSynctableCfg(tableId, tableName);
                    list.add(etlSynctableCfg);
                }
                 return JsonResponse.getSucc(list);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        } else {

        }

        return JsonResponse.getFail();
    }

    public JsonResponse generate(String rows, String sourceDbId, String targetDbId, String savePath) {
        EtlDatasourceCfg sourceCfg = etlDatasourceCfgDao.select(sourceDbId);
        EtlDatasourceCfg targetCfg = etlDatasourceCfgDao.select(targetDbId);
        List list = new ArrayList();

        // 分割表名称字符串
        String[] tableNameArr = rows.split(",");
        // 循环表名称
        for (String tableName:tableNameArr) {
            // 获取表字段
            EtlSynctableCfg tableValue = getTableColValue(sourceCfg, tableName);
            //调用模板方法，生成转换
            frGenerTrans(sourceCfg, targetCfg, tableValue, savePath);
        }

        return JsonResponse.getSucc();
    }

    private void frGenerTrans(EtlDatasourceCfg sourceCfg, EtlDatasourceCfg targetCfg, EtlSynctableCfg tableValue, String savePathStr) {
        // 整理数据
        Map<String, Object> root = new HashMap<String, Object>();
        root.put("source", sourceCfg);
        root.put("target", targetCfg);
        root.put("tableValue", tableValue);
        String ktrName = "同步" + sourceCfg.getConnName() + "[" + tableValue.getTableName() + "]到" + targetCfg.getConnName() + ".ktr";
        root.put("ktrName", ktrName);

        // 定义输出路径
        String filePath = savePathStr;
        int lastNum = filePath.lastIndexOf("\\");
        String savePath = filePath.substring(0, lastNum + 1);

        // 发送给fr模板并生成
        FmTemplate fmTemplate = new FmTemplate();
        fmTemplate.fprint("ktr1.0.ftl", root, ktrName, savePath);


    }

    private EtlSynctableCfg getTableColValue(EtlDatasourceCfg sourceCfg, String tableName) {
        // 获取数据库连接
        String driver = DbUtil.getDriverClassName(sourceCfg.getType());
        String dbUrl = DbUtil.getDbUrl(sourceCfg.getType(), sourceCfg.getServerAddr(), sourceCfg.getPort(), sourceCfg.getName());
        Connection conn = DbUtil.getDbConn(driver, dbUrl, sourceCfg.getUser(), sourceCfg.getPassword());

        if (conn != null) {
            // 获取查询表字段sql
            String selectTableListSql = DbUtil.getSelectTableColListSql(sourceCfg.getType());
            PreparedStatement ps = null;
            ResultSet rs = null;
            try {
                // 执行sql，封装为实体
                ps = conn.prepareStatement(selectTableListSql);
                ps.setString(1, sourceCfg.getName());
                ps.setString(2, tableName);
                rs = ps.executeQuery();
                EtlSynctableCfg etlSynctableCfg = new EtlSynctableCfg(tableName, tableName);
                List<EtlSynctableCol> list = new ArrayList<EtlSynctableCol>();
                while (rs.next()) {
                    EtlSynctableCol tableCol = new EtlSynctableCol();
                    tableCol.setTableCatalog(rs.getString("TABLE_CATALOG"));
                    tableCol.setTableSchema(rs.getString("TABLE_SCHEMA"));
                    tableCol.setTableName(rs.getString("TABLE_NAME"));
                    tableCol.setColumnName(rs.getString("COLUMN_NAME"));
                    tableCol.setOrdinalPosition(rs.getLong("ORDINAL_POSITION"));
                    tableCol.setColumnDefault(rs.getString("COLUMN_DEFAULT"));
                    tableCol.setIsNullable(rs.getString("IS_NULLABLE"));
                    tableCol.setDataType(rs.getString("DATA_TYPE"));
                    tableCol.setCharacterMaximum(rs.getLong("CHARACTER_MAXIMUM_LENGTH"));
                    tableCol.setCharacterOctetLength(rs.getLong("CHARACTER_OCTET_LENGTH"));
                    tableCol.setNumericPrecision(rs.getLong("NUMERIC_PRECISION"));
                    tableCol.setNumericScale(rs.getLong("NUMERIC_SCALE"));
                    tableCol.setDatetimePrecision(rs.getLong("DATETIME_PRECISION"));
                    tableCol.setCharacterSetName(rs.getString("CHARACTER_SET_NAME"));
                    tableCol.setCollationName(rs.getString("COLLATION_NAME"));
                    tableCol.setColumnType(rs.getString("COLUMN_TYPE"));
                    tableCol.setColumnKey(rs.getString("COLUMN_KEY"));
                    tableCol.setExtra(rs.getString("EXTRA"));
                    tableCol.setPrivileges(rs.getString("PRIVILEGES"));
                    tableCol.setColumnComment(rs.getString("COLUMN_COMMENT"));
                    tableCol.setGenerationExpression(rs.getString("GENERATION_EXPRESSION"));
                    list.add(tableCol);
                }
                etlSynctableCfg.setTableColList(list);
                return etlSynctableCfg;
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        } else {

        }
        return null;
    }
}
